﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using APPLICATION.ErrorDict;

namespace APPLICATION.UserManager
{
    class UMdataAccess
    {
        private ErrFinder ErrFinder1 = new ErrFinder();

        #region Select opereation
        public int filldtStaff()
        {
            SqlConnection SqlConn = new SqlConnection(globals.APPCONNSTRING);
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "tsp_dtStaff";
            SqlCmd.Connection = SqlConn;
            SqlCmd.Parameters.Add("@Action", SqlDbType.Int).Value = 3;
            SqlDataAdapter SqlDa = new SqlDataAdapter(SqlCmd);
            try
            {
                GlobalDataFile.UserManagerDataSet.dtStaff.Clear();                
                return SqlDa.Fill(GlobalDataFile.UserManagerDataSet.dtStaff);
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return 0;
            }
        }

        public int fillStUsers()
        {
            SqlConnection SqlConn = new SqlConnection(globals.APPCONNSTRING);
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "tsp_stUsers";
            SqlCmd.Connection = SqlConn;
            SqlCmd.Parameters.Add("Action", SqlDbType.Int).Value = 3;
            SqlDataAdapter SqlDa = new SqlDataAdapter(SqlCmd);
            try
            {
                GlobalDataFile.UserManagerDataSet.stUsers.Clear();
                return SqlDa.Fill(GlobalDataFile.UserManagerDataSet.stUsers);
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return 0;
            }
        }      

        public int fillstRoles()
        {
            SqlConnection SqlConn = new SqlConnection(globals.APPCONNSTRING);
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "tsp_stRoles";
            SqlCmd.Connection = SqlConn;
            SqlCmd.Parameters.Add("Action", SqlDbType.Int).Value = 3;
            SqlDataAdapter SqlDa = new SqlDataAdapter(SqlCmd);
            try
            {
                GlobalDataFile.UserManagerDataSet.stRoles.Clear();
                return SqlDa.Fill(GlobalDataFile.UserManagerDataSet.stRoles);
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return 0;
            }
        }

        public int fillstActions()
        {
            SqlConnection SqlConn = new SqlConnection(globals.APPCONNSTRING);
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "tsp_stActions";
            SqlCmd.Connection = SqlConn;
            SqlCmd.Parameters.Add("Action", SqlDbType.Int).Value = 3;
            SqlDataAdapter SqlDa = new SqlDataAdapter(SqlCmd);
            try
            {
                GlobalDataFile.UserManagerDataSet.stActions.Clear();
                return SqlDa.Fill(GlobalDataFile.UserManagerDataSet.stActions);
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return 0;
            }
        }

        //fn_GetUserActions
        public int FillUsersInPerAction(string ActionsID)
        {
            SqlConnection SqlConn = new SqlConnection(globals.APPCONNSTRING);
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.CommandType = CommandType.Text;
            SqlCmd.CommandText = "SELECT  UserID, UserN, StaffID, DepID, PostID, CityID, StaffName, addr, Gender, DBorn, DWork, PerNum, TabNum, postName, depName, CityName, cast(isGrant as bit) as isGrant, ActionsID FROM dbo.fn_GetUserActions(" + ActionsID + ")";
            SqlCmd.Connection = SqlConn;
            SqlDataAdapter SqlDa = new SqlDataAdapter(SqlCmd);
            try
            {
                GlobalDataFile.UsersInPerAction.Clear();
                return SqlDa.Fill(GlobalDataFile.UsersInPerAction);
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return 0;
            }
        }
        //fn_GetRolesActions
        public int FillRolesInPerAction(string ActionsID)
        {
            SqlConnection SqlConn = new SqlConnection(globals.APPCONNSTRING);
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.CommandType = CommandType.Text;
            SqlCmd.CommandText = "SELECT * FROM dbo.fn_GetRolesActions(" + ActionsID + ")";
            SqlCmd.Connection = SqlConn;
            SqlDataAdapter SqlDa = new SqlDataAdapter(SqlCmd);
            try
            {
                GlobalDataFile.RolesInPerAction.Clear();
                return SqlDa.Fill(GlobalDataFile.RolesInPerAction);
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return 0;
            }
        }

        //fn_GetUserRoles1
        public int FillRolesInPerUser(string UserID)
        {

            SqlConnection SqlConn = new SqlConnection(globals.APPCONNSTRING);
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.CommandType = CommandType.Text;
            SqlCmd.CommandText = "SELECT RolesID, UserID, URolesID, RolesN, CAST(isGrant AS bit) as isGrant FROM dbo.fn_GetUserRoles1(" + UserID + ")";
            SqlCmd.Connection = SqlConn;
            SqlDataAdapter SqlDa = new SqlDataAdapter(SqlCmd);
            try
            {
                GlobalDataFile.RolesInPerUser.Clear();
                return SqlDa.Fill(GlobalDataFile.RolesInPerUser);
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return 0;
            }
        }
        //fn_GetUserRoles2
        public int FillUsersInPerRole(string RoleID)
        {

            SqlConnection SqlConn = new SqlConnection(globals.APPCONNSTRING);
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.CommandType = CommandType.Text;
            SqlCmd.CommandText = "SELECT UserID, UserN, StaffID, DepID, PostID, CityID, StaffName, addr, Gender, DBorn, DWork, PerNum, TabNum, postName, depName, CityName, CAST(isGrant AS bit) as isGrant, RolesID FROM dbo.fn_GetUserRoles2(" + RoleID + ")";
            SqlCmd.Connection = SqlConn;
            SqlDataAdapter SqlDa = new SqlDataAdapter(SqlCmd);
            try
            {
                GlobalDataFile.UsersInPerRole.Clear();
                return SqlDa.Fill(GlobalDataFile.UsersInPerRole);
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return 0;
            }
        }

        //fn_TGetActions1
        public int FillActionsInPerUser(string UserID)
        {           
            SqlConnection SqlConn = new SqlConnection(globals.APPCONNSTRING);
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.CommandType = CommandType.Text;
            SqlCmd.CommandText = "SELECT CodeID, hName, mgrid, hrclvl, hrccrt, hCode, hccrt, cast(isGrant as bit) as isGrant  FROM dbo.fn_TGetActions1(" + UserID + ")";
            SqlCmd.Connection = SqlConn;
            SqlDataAdapter SqlDa = new SqlDataAdapter(SqlCmd);
            try
            {
                GlobalDataFile.ActionsInPerUser.Clear();
                return SqlDa.Fill(GlobalDataFile.ActionsInPerUser);
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return 0;
            }  
        }
        //fn_TGetActions2
        public int FillActionsInPerRole(string RoleID)
        {
            SqlConnection SqlConn = new SqlConnection(globals.APPCONNSTRING);
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.CommandType = CommandType.Text;
            SqlCmd.CommandText = "SELECT CodeID, hName, mgrid, hrclvl, hrccrt, hCode, hccrt, cast(isGrant as bit) as isGrant  FROM dbo.fn_TGetActions2(" + RoleID + ")";
            SqlCmd.Connection = SqlConn;
            SqlDataAdapter SqlDa = new SqlDataAdapter(SqlCmd);
            try
            {
                GlobalDataFile.ActionsInPerRole.Clear();
                return SqlDa.Fill(GlobalDataFile.ActionsInPerRole);
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return 0;
            }
        }

        #endregion

        #region Insert, update and delete operations
        public bool tx_UMSRGrantURA(SqlParameter sqlPar)
        {
            SqlConnection SqlConn = new SqlConnection(globals.APPCONNSTRING);
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.Connection = SqlConn;
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "tx_UMSRGrantURA";
            SqlCmd.Parameters.Add(sqlPar);
            try
            {
                SqlConn.Open();
                SqlCmd.ExecuteNonQuery();
                return true;
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return false;
            }
            finally
            {
                SqlConn.Close();
            }
      
        }

        public bool addOrUpdUser(SqlParameter[] SqlParams, System.Windows.Forms.BindingSource bsUserForCancelEdit,System.Windows.Forms.BindingSource bsStaffForCancelEdit ) 
        {
            SqlConnection SqlConn = new SqlConnection(globals.APPCONNSTRING);
            SqlCommand SqlCmd=new SqlCommand();           
            SqlCmd.Connection=SqlConn;
            SqlCmd.CommandType=System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "tsp_UMStaff";

            SqlCmd.Parameters.AddRange(SqlParams);

            try
            {
                //
                SqlConn.Open();
                if (SqlCmd.ExecuteNonQuery() != 0)
                {
                    bsUserForCancelEdit.CancelEdit();
                    bsStaffForCancelEdit.CancelEdit();
                    InsOrUpddataTable(GlobalDataFile.UserManagerDataSet.Tables["dtStaff"], SqlCmd.Parameters["@StaffID"]);
                    InsOrUpddataTable(GlobalDataFile.UserManagerDataSet.Tables["stUsers"], SqlCmd.Parameters["@UserID"]);                    
                    
                }
                return true;
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return false;
            }
            finally
            {
                SqlConn.Close();
            }
          
        }

        public bool addOrUpdRole(SqlParameter[] SqlParams, System.Windows.Forms.BindingSource bs)
        {
            SqlConnection SqlConn = new SqlConnection(globals.APPCONNSTRING);
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.Connection = SqlConn;
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "tsp_stRoles";

            SqlCmd.Parameters.AddRange(SqlParams);

            try
            {
                //
                SqlConn.Open();
                if (SqlCmd.ExecuteNonQuery() != 0)
                {
                    bs.CancelEdit();
                    InsOrUpddataTable(GlobalDataFile.UserManagerDataSet.Tables["stRoles"], SqlCmd.Parameters["@RolesID"]);
                }
                return true;
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return false;
            }
            finally
            {
                SqlConn.Close();
            }
          
        }

        public bool addOrUpdateAction(SqlParameter[] SqlParams, System.Windows.Forms.BindingSource bs)
        {
            SqlConnection SqlConn = new SqlConnection(globals.APPCONNSTRING);
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.Connection = SqlConn;
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "tsp_stActions";

            SqlCmd.Parameters.AddRange(SqlParams);

            try
            {
                //
                SqlConn.Open();
                if (SqlCmd.ExecuteNonQuery() != 0)
                {
                    bs.CancelEdit();
                    InsOrUpddataTable(GlobalDataFile.UserManagerDataSet.stActions, SqlCmd.Parameters["@CodeID"]);
                }
                return true;
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return false;
            }
            finally
            {
                SqlConn.Close();
            }
        }

        public bool deleteUser(int UserID)
        {
            SqlConnection SqlConn = new SqlConnection(globals.APPCONNSTRING);
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.Connection = SqlConn;
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "tsp_stUsers";

            SqlCmd.Parameters.Add("Action", SqlDbType.Int).Value = 2;
            SqlCmd.Parameters.Add("UserID", SqlDbType.Int).Value = UserID;

            try
            {
                //
                SqlConn.Open();
                SqlCmd.ExecuteNonQuery();
                this.DeleteDataInDataTable(GlobalDataFile.UserManagerDataSet.stUsers,UserID);
                return true;
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return false;
            }
            finally
            {
                SqlConn.Close();
            }
        }

        public bool deleteRole(int RoleID)
        {
            SqlConnection SqlConn = new SqlConnection(globals.APPCONNSTRING);
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.Connection = SqlConn;
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "tsp_stRoles";

            SqlCmd.Parameters.Add("Action", SqlDbType.Int).Value = 2;
            SqlCmd.Parameters.Add("RolesID", SqlDbType.Int).Value = RoleID;

            try
            {
                //
                SqlConn.Open();
                SqlCmd.ExecuteNonQuery();                
                this.DeleteDataInDataTable(GlobalDataFile.UserManagerDataSet.Tables["stRoles"], RoleID);                
                return true;
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return false;
            }
            finally
            {
                SqlConn.Close();
            }
        }

        public bool deleteAction(int ActionID)
        {
            SqlConnection SqlConn = new SqlConnection(globals.APPCONNSTRING);
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.Connection = SqlConn;
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "tsp_stActions";

            SqlCmd.Parameters.Add("Action", SqlDbType.Int).Value = 2;
            SqlCmd.Parameters.Add("CodeID", SqlDbType.Int).Value = ActionID;

            try
            {
                //
                SqlConn.Open();
                SqlCmd.ExecuteNonQuery();
                this.DeleteDataInDataTable(GlobalDataFile.UserManagerDataSet.stActions, ActionID);
                return true;
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return false;
            }
            finally
            {
                SqlConn.Close();
            }
        }
        #endregion

        #region Manage datatable(cachdata)
        private object[] GetObjectArray(SqlDataReader SqlDr)
        {
            int i = 0;
            object[] MyObj = new object[SqlDr.FieldCount];
            while (SqlDr.FieldCount > i)
            {
                MyObj[i] = SqlDr[i];
                i += 1;
            }
            return MyObj;
        }

        private bool InsOrUpddataTable(DataTable Dt, SqlParameter SqlPar)
        {
            string SelectCommand = "select * from " + Dt.TableName + " where " + SqlPar.ParameterName.Substring(1, SqlPar.ParameterName.Length-1) + "=" + SqlPar.Value.ToString();
            GetConnectionString Sett = new GetConnectionString();
            SqlConnection SqlConnection1 = new SqlConnection(Sett.ConnectionSrting());
            SqlCommand Sqlcmd1 = new SqlCommand(SelectCommand, SqlConnection1);
            SqlDataReader SqlDr;

            try
            {
                SqlConnection1.Open();
                SqlDr = Sqlcmd1.ExecuteReader();
                if (SqlDr.Read())
                {                    
                    Dt.BeginLoadData();
                    //Finds and updates a specific row. If no matching row is found, a new row is created using the given values
                    Dt.LoadDataRow(GetObjectArray(SqlDr), true);
                    Dt.EndLoadData();
                    Dt.AcceptChanges();
                    return true;
                }
                return false;
            }
            catch (SqlException e1)
            {
                string ErrMsg = ErrFinder1.GetErrMessage(e1.GetType().Name, e1.ErrorCode.ToString(), e1.Message);
                System.Windows.Forms.MessageBox.Show(ErrMsg, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);                
            }
            finally
            {
                SqlConnection1.Close();
            }
            return false;
        }

        private void DeleteDataInDataTable(DataTable Dt, int ID)
        {
            DataRow dr;
            dr = Dt.Rows.Find(ID);
            Dt.Rows.Remove(dr);
            Dt.AcceptChanges();
        }
        #endregion

    }
}
